﻿using System;
using System.Collections.Generic;
using System.Linq;

using CommonLib;

namespace DbLayer
{
    public class DbBridge : IDbProxy
    {
        public static IDbProxy GetInstance()
        {
            if (_instance == null)
                _instance = new DbBridge();

            return _instance;
        }

        private static DbBridge _instance;

        public void AddToArticleWords(ArticleWords aw)
        {
            throw new NotImplementedException();
        }

        public void SaveChanges()
        {
            throw new NotImplementedException();
        }

        public void AddToWords(Words dbWord)
        {
            throw new NotImplementedException();
        }

        public void AddAuthorArticle(Articles articleD, Authors author, Test db)
        {
            if (db == null)
                db = new Test(CN);

            var authorArticle = new AuthorArticles { Articles = articleD, Authors = author };

            db.AuthorArticles.InsertOnSubmit(authorArticle);

            db.SubmitChanges();
        }

        public void AddToArticles(Articles article)
        {
            using (var db = new Test(CN))
            {
                db.Articles.InsertOnSubmit(article);

                db.SubmitChanges();
            }
        }

        public Authors AddToAuthors(string authorName)
        {
            using (var db = new Test(CN))
            {
                var author = db.Authors.Where(a => a.NameSurname == authorName).FirstOrDefault();

                if (author != null)
                    return author;

                var newAuthor = new Authors { NameSurname = authorName };

                db.Authors.InsertOnSubmit(author);

                db.SubmitChanges();

                return newAuthor;
            }

        }

        public List<Words> AddWords(ICollection<string> words, Test db)
        {
            if (db == null)
                db = new Test(CN);

            var res = new List<Words>();
            var wordList = new List<string>(words);

            var list = words.Where(word => db.Words.Any(dbWord => dbWord.WordText == word)).ToList();

            foreach (var wIter in list)
                wordList.Remove(wIter);

            foreach (var dbWord in wordList.Select(word => new Words { WordText = word }))
            {
                res.Add(dbWord);

                db.Words.InsertOnSubmit(dbWord);
            }

            db.SubmitChanges();

            foreach (var v in list)
            {
                string v1 = v;
                var w = db.Words.Where(word => word.WordText == v1).FirstOrDefault();
                if (w != null)
                    res.Add(w);
            }

            return res;
        }

        public void AddArticleDb(WordStatArticle article)
        {
            using (var db = new Test(CN))
            {
                var author = AddAuthor(article.Author, db);

                if (author == null)
                    return;

                var articleDb = AddArticle(article.Title, article.Date, article.Text, article.Url, article.Source, db);

                if (articleDb == null)
                    return;

                AddAuthorArticle(articleDb, author, db);

                var words = PrepareWords(article.Words);

                var dbWords = AddWords(words.Keys, db);

                AddArticleWords(articleDb, dbWords, words, db);

                db.SubmitChanges();
            }
        }

        Dictionary<string, int> IDbProxy.GetAllWordsData(int page, int itemOnPages, bool sortByName,string source)
        {
            using (var db = new Test(CN))
            {
                var toSkip = page * itemOnPages;

                // todo: оптимизировать
                if (source == null)
                {
                    var res = ((from wd in db.Words
                                join aw in db.ArticleWords on wd.WordId equals aw.WordId into awres
                                select new { wd.WordText, WordCount = awres.Sum(a => a.WordCount) }
                               ).OrderBy(wd => wd.WordText).OrderBy(wd => wd.WordCount)).ToList().Skip(toSkip).Take(itemOnPages);

                    return res.Where(iter => iter.WordCount != null).ToDictionary(iter => iter.WordText,
                                                                                  iter => iter.WordCount.Value);
                }
                else
                {
                    var s = (from a in db.Articles
                             join aw in db.ArticleWords on a.ArticleId equals aw.ArticleId
                             where a.Source == source
                             group new { aw, aw.WordId } by aw.WordId into aWord
                             select new { aWord, Count = aWord.Sum(a => a.aw.WordCount) }).ToList();

                    var dic = new List<TmpClass>();

                    foreach (var v in s)
                    {
                        var text = db.Words.Where(w => w.WordId == v.aWord.Key.Value).Select(w => w.WordText).FirstOrDefault();
                        dic.Add(new TmpClass { Count = v.Count ?? 0, Text = text });
                    }

                    return dic.OrderBy(o => o.Count).Skip(toSkip).Take(itemOnPages).ToDictionary(t => t.Text, c => c.Count);
                }
            }
        }

        private class TmpClass
        {
            public string Text { get; set; }

            public int Count { get; set; }
        }

        public IDictionary<WordStatArticle, int> GetArticles(string wordText, string currentSource)
        {
            using (var db = new Test(CN))
            {
                var wordId = (from w in db.Words where wordText == w.WordText select w.WordId).FirstOrDefault();

                var articlids = (from wa in db.ArticleWords
                                 where wa.WordId == wordId
                                 select new { wa.ArticleId, wa.WordCount });

                var dic = articlids.ToDictionary(articlid => articlid.ArticleId.Value, articlid => articlid.WordCount.Value);

                var list = dic.Select(d => d.Key);

                var articles = from a in db.Articles where list.Contains(a.ArticleId) select new { a.ArticleTitle, a.ArticleUrl, a.ArticleId, a.ArticleText };

                var res = new Dictionary<WordStatArticle, int>();

                foreach (var article in articles)
                {
                    var a = new WordStatArticle { Title = article.ArticleTitle, Url = article.ArticleUrl, Text = article.ArticleText };

                    res.Add(a, dic[article.ArticleId]);
                }

                return res;
            }
        }

        private void AddArticleWords(Articles articleDb, List<Words> dbWords, IDictionary<string, int> words, Test db)
        {
            foreach (var dbWord in dbWords)
            {
                if (!words.ContainsKey(dbWord.WordText))
                    continue;

                var aw = new ArticleWords { Articles = articleDb, Words = dbWord, WordCount = words[dbWord.WordText] };

                db.ArticleWords.InsertOnSubmit(aw);
            }

            db.SubmitChanges();
        }

        private static IDictionary<string, int> PrepareWords(Dictionary<string, int> words)
        {
            return words.ToDictionary(word => word.Key.ToLower().Trim(), word => word.Value);
        }

        private Articles AddArticle(string title, DateTime date, string text, string url, string source, Test db)
        {
            var article = new Articles
                              {
                                  ArticleTitle = title,
                                  ArticleText = text,
                                  ArticleUrl = url,
                                  ArticleDate = date,
                                  Source = source
                              };

            db.Articles.InsertOnSubmit(article);

            db.SubmitChanges();

            return article;
        }

        private Authors AddAuthor(string authorName, Test db)
        {
            var author = db.Authors.Where(a => a.NameSurname == authorName).FirstOrDefault();

            if (author != null)
                return author;

            var newAuthor = new Authors { NameSurname = authorName };

            db.Authors.InsertOnSubmit(newAuthor);

            db.SubmitChanges();

            return newAuthor;
        }

        public List<string> GetAllSoyurces()
        {
            using (var db = new Test(CN))
            {
                return (from a in db.Articles select a.Source).Distinct().ToList();
            }

            return new List<string>();
        }

        private const string CN = @"Data Source=D:\zagir\Development\wordstat\WordStat\test.sdf";
    }
}
